import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLXML;
import java.sql.SQLException;

/**
 * @作者: songjing20
 * @时间: 2022/8/29
 * @测试点: JDBC支持对xml字段进行select，update，insert，delete
 */

public class Opengauss_Function_Datatype_Xml_Case0054 {
    public static Connection connectToDatabase(String[] args) {
        if (args.length < 4) {
            System.out.print("need para: host:port, dbname, user, password");
            return null;
        } else {
            Connection connection = null;
            try {
                String url = "jdbc:postgresql://" + args[0] + "/" + args[1];
                connection = DriverManager.getConnection(url, args[2], args[3]);
            } catch (Exception var10) {
                var10.printStackTrace();
                return null;
            }
            return connection;
        }
    }

    public static void main(String[] args) {
        try (Connection conn = connectToDatabase(args)) {
            // 建表，xml类型字段
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("drop table if exists t_xml_0054 cascade;" +
                        "create table t_xml_0054(c1 int, c2 xml, c3 xml);");
            }
            // 插入 xml数据
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("insert into t_xml_0054 values(1, 'asdf', 'asdddf');");
            }

            SQLXML info = conn.createSQLXML();
            info.setString("<book>《孙子兵法》</book>");
            String query = "INSERT INTO t_xml_0054(c1, c2, c3) VALUES (?, ?, ?);";
            try (PreparedStatement pstmt = conn.prepareStatement(query)) {
                pstmt.setInt(1, 123);
                pstmt.setSQLXML(2, null);
                pstmt.setSQLXML(3, info);
                pstmt.executeUpdate();
            }

            // 查询xml数据
            query = "SELECT * FROM t_xml_0054 order by 1;";
            try (PreparedStatement pstmt = conn.prepareStatement(query)) {
                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) {
                        SQLXML c3 = rs.getSQLXML(3);
                        System.out.println(rs.getInt(1) + " | " + rs.getString(2) + " | " + c3.getString());
                    }
                }
            }

            // 更新xml数据
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("update t_xml_0054 set c3 = xmlparse(content 'update values') where c1 = 123;");
            }

            query = "UPDATE t_xml_0054 SET c2 = ? WHERE c1 = 1;";
            SQLXML xmlData = conn.createSQLXML();
            xmlData.setString("<update>testcolumn</update>");
            try (PreparedStatement pstmt = conn.prepareStatement(query)) {
                pstmt.setSQLXML(1, xmlData);
                pstmt.executeUpdate();
            }

            query = "SELECT c1, c2, c3 FROM t_xml_0054 order by 1;";
            try (PreparedStatement pstmt = conn.prepareStatement(query)) {
                try (ResultSet rs = pstmt.executeQuery()) {
                    System.out.println("select result after update:");
                    while (rs.next()) {
                        SQLXML c3 = rs.getSQLXML(3);
                        System.out.println(rs.getInt(1) + " | " + rs.getString(2) + " | " + c3.getString());
                    }
                }
            }

            // 删除xml数据
            try (PreparedStatement pstmt = conn.prepareStatement("DELETE FROM t_xml_0054;")) {
                if (pstmt.executeUpdate() != 1) {
                    System.out.println("Delete success");
                }
            }
            // 删表
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("drop table if exists t_xml_0054 cascade;");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
